package com.bookyourtrek.model;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.joda.time.DateTime;
import org.joda.time.Days;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import com.bookyourtrek.util.DbConnection;
import com.bookyourtrek.util.RuntimeSettings;
import com.mysql.jdbc.Statement;
import com.sun.xml.internal.bind.v2.runtime.unmarshaller.XsiNilLoader.Array;

public class OrderModel {

	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private String sqlQuery = "";
	private Connection conn = null;
	
	/**
	 * create order entry into database
	 * @author ananda
	 * @param ticketid
	 * @param fullname
	 * @param email
	 * @param address
	 * @param selectedquantity
	 * @param totalprice
	 * @param phone
	 * @param status
	 * @return
	 */
	public BigInteger createOrder(BigInteger ticketid, String fullname, String email,
			String address, Integer selectedquantity, double totalprice,
			String phone, Integer status) {
		BigInteger orderid = null;
		sqlQuery = "INSERT INTO `bookyourtrek`.`order`"
				+ " (`ticketid`, `name`, `useremail`, `address`, `quantity`," +
				" `amount`, `phone`, `status`) VALUES (?,?,?,?,?,?,?,?);";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS);
			ps.setLong(1, ticketid.longValue());
			ps.setString(2, fullname);
			ps.setString(3, email);
			ps.setString(4, address);
			ps.setInt(5, selectedquantity);
			ps.setDouble(6, totalprice);
			ps.setString(7, phone);
			ps.setString(8, status.toString());
			ps.executeUpdate();
			//get id of inserted record
			ResultSet keys = ps.getGeneratedKeys();    
			keys.next();  
			orderid = BigInteger.valueOf(keys.getLong(1));
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return orderid;
		
	}

	/**
	 * confirm payment via cod
	 * @author ananda
	 * @param orderid
	 */
	public void confirmPayment(BigInteger orderid) {
		sqlQuery = "UPDATE bookyourtrek.order SET status = ? WHERE orderid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setInt(1, RuntimeSettings.ORDERCONFIRMED);
			ps.setLong(2, orderid.longValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * get order summary from database
	 * @author ananda
	 * @param useremail
	 * @return
	 */
	public ArrayList<OrderSummaryPojo> getordersummarybyuseremail(String useremail) {
		ArrayList<OrderSummaryPojo> osp = new ArrayList<OrderSummaryPojo>();
		sqlQuery = "SELECT orderid, ticketid, name, useremail," +
				" address, quantity, amount, phone, status," +
				" timestamp FROM bookyourtrek.order"
				+ " where useremail = ? ORDER BY timestamp DESC;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setString(1, useremail);
			rs = ps.executeQuery();
			while (rs.next()) {
				OrderSummaryPojo orsumpo = new OrderSummaryPojo();
				orsumpo.setOrderid(BigInteger.valueOf(rs.getLong(1)));
				orsumpo.setTicketid(BigInteger.valueOf(rs.getLong(2)));
				orsumpo.setName(rs.getString(3));
				orsumpo.setUseremail(rs.getString(4));
				orsumpo.setAddress(rs.getString(5));
				orsumpo.setQuantity(rs.getInt(6));
				orsumpo.setAmount(rs.getDouble(7));
				orsumpo.setPhone(rs.getString(8));
				orsumpo.setStatus(rs.getString(9));
				SimpleDateFormat toFormat = new SimpleDateFormat("d MMM, yyyy");
				orsumpo.setOrderdatetime(toFormat.format(rs.getTimestamp(10)));
				osp.add(orsumpo);
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return osp;
	}

	public void cancelPayment(BigInteger orderid) {
		sqlQuery = "UPDATE bookyourtrek.order SET status = ? WHERE orderid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setInt(1, RuntimeSettings.ORDERCANCLED);
			ps.setLong(2, orderid.longValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * check user canceling ticket is valid or not 
	 * @author ananda
	 * @param orderid
	 * @param email
	 * @return
	 */
	public boolean checkvaliduser(BigInteger orderid, String email) {
		sqlQuery = "SELECT useremail FROM bookyourtrek.order"
				+ " where orderid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, orderid.longValue());
			rs = ps.executeQuery();
			while (rs.next()) {
				if(email.equalsIgnoreCase(rs.getString(1))) {
					return true;
				}
				else {
					return false;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		
		return false;
	}

	/**
	 * cancel the order
	 * @author ananda
	 * @param cancelorderid
	 */
	public void cancelorder(BigInteger cancelorderid, String useremail) {
		sqlQuery = "UPDATE bookyourtrek.order SET status = ? WHERE orderid = ? AND useremail = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setInt(1, RuntimeSettings.ORDERCANCLED);
			ps.setLong(2, cancelorderid.longValue());
			ps.setString(3, useremail);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	/**
	 * check wether oderid exist and associated with valid user
	 * @author ananda
	 * @param cancelorderid
	 * @param useremail
	 * @return true/false
	 */
	public boolean orderIdExistWithUser(BigInteger cancelorderid,
			String useremail) {
		sqlQuery = "SELECT useremail FROM bookyourtrek.order"
				+ " where orderid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, cancelorderid.longValue());
			rs = ps.executeQuery();
			while (rs.next()) {
				if(useremail.equalsIgnoreCase(rs.getString(1))) {
					return true;
				}
				else {
					return false;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		
		return false;
	}

	public boolean ticketAlreadyCancelled(BigInteger cancelorderid, String useremail) {
		sqlQuery = "SELECT status FROM bookyourtrek.order"
				+ " where orderid = ? AND useremail = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, cancelorderid.longValue());
			ps.setString(2, useremail);
			rs = ps.executeQuery();
			while (rs.next()) {
				if(rs.getString(1).equalsIgnoreCase(RuntimeSettings.ORDERCANCLED.toString())) {
					return true;
				}
				else {
					return false;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		
		return false;
	}

	public boolean ticketEndDatePassed(BigInteger cancelorderid) {
		sqlQuery = "SELECT enddate FROM bookyourtrek.ticket"
				+ " where ticketid = ?";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, cancelorderid.longValue());
			rs = ps.executeQuery();
			while (rs.next()) {
				Date enddate = rs.getDate(1);
				DateTimeFormatter formatter = DateTimeFormat.forPattern("dd-mm-yy");
				DateFormat dateFormat = new SimpleDateFormat("dd-MM-yy");
				   //get current date time with Date()
				   Date date = new Date();
				   //System.out.println(dateFormat.format(date)); don't print it, but save it!
				   String currentdate = dateFormat.format(date);
				   String enddateformatted= dateFormat.format(enddate);
				   //System.out.println(currentdate);
				//String currentdate = new Date().toString();
				//System.out.println(Days.daysBetween(new DateTime(formatter.parseDateTime(date)), new DateTime(formatter.parseDateTime(tktenddate))).getDays());
				if(Days.daysBetween(new DateTime(formatter.parseDateTime(currentdate)), new DateTime(formatter.parseDateTime(enddateformatted))).getDays() < 1){
					return true;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		
		return false;
	}

	public ArrayList<OrderSummaryPojo> getOrderSummaryByID(BigInteger orderid) {
		ArrayList<OrderSummaryPojo> osp = new ArrayList<OrderSummaryPojo>();
		sqlQuery = "SELECT orderid, ticketid, name, useremail," +
				" address, quantity, amount, phone, status," +
				" timestamp FROM bookyourtrek.order"
				+ " where orderid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, orderid.longValue());
			rs = ps.executeQuery();
			while (rs.next()) {
				OrderSummaryPojo orsumpo = new OrderSummaryPojo();
				orsumpo.setOrderid(BigInteger.valueOf(rs.getLong(1)));
				orsumpo.setTicketid(BigInteger.valueOf(rs.getLong(2)));
				orsumpo.setName(rs.getString(3));
				orsumpo.setUseremail(rs.getString(4));
				orsumpo.setAddress(rs.getString(5));
				orsumpo.setQuantity(rs.getInt(6));
				orsumpo.setAmount(rs.getDouble(7));
				orsumpo.setPhone(rs.getString(8));
				orsumpo.setStatus(rs.getString(9));
				SimpleDateFormat toFormat = new SimpleDateFormat("d MMM, yyyy");
				orsumpo.setOrderdatetime(toFormat.format(rs.getTimestamp(10)));
				osp.add(orsumpo);
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return osp;
	}
}
